Set Auto Increment Starting Value {postgres}

들어가기 전에,

sequence 이름찾는 방법부터 알아보자.

SELECT pg_get_serial_sequence('image', 'imgId');

Screenshot 2025-03-08 at 22.53.22.png

또는 모든 sequence를 찾고싶다면..

SELECT * FROM pg_sequences;

Screenshot 2025-03-08 at 22.55.55.png

sequence 이름 찾는 방법 (GPT 응답)

Since your sequence is named public."image_imgId_seq", you can reconfigure the increment value using the following steps:


1. Update the Increment Value

To change the increment value (how much the sequence increases per new entry):

ALTER SEQUENCE public."image_imgId_seq" INCREMENT BY 1;

(Change 1 to any desired increment.)


2. Restart the Sequence with a Specific Value

If you want the next generated ID to start at 1000, run:

ALTER SEQUENCE public."image_imgId_seq" RESTART WITH 1000;

Alternatively, use:

SELECT setval('public."image_imgId_seq"', 1000, false);

3. Verify the Change

Run:

SELECT nextval('public."image_imgId_seq"');

This should return 1000 (or your desired starting value).


4. Ensure the Sequence is Linked to Your Column

Make sure the image_imgId column uses the sequence:

SELECT column_default
FROM information_schema.columns
WHERE table_name = 'image' AND column_name = 'imgId';

If the output does not contain nextvalregclass, manually link the sequence:

ALTER TABLE image ALTER COLUMN imgId SET DEFAULT nextval('public."image_imgId_seq"');

5. Test with an Insert

INSERT INTO image DEFAULT VALUES RETURNING imgId;

This should return 1000 if everything is configured correctly.


Summary

Set increment value: ALTER SEQUENCE public."image_imgId_seq" INCREMENT BY 1;
Restart at 1000: ALTER SEQUENCE public."image_imgId_seq" RESTART WITH 1000;
Manually set value: SELECT setval('public."image_imgId_seq"', 1000, false);
Ensure the column uses the sequence: ALTER TABLE image ALTER COLUMN imgId SET DEFAULT nextval('public."image_imgId_seq"');
Test with INSERT

Let me know if you need further help! 🚀